package com.huixin.zhang.common.utils;

import com.alibaba.fastjson.JSONObject;
import com.huixin.zhang.persistence.dto.ExcelHeaderDto;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;

import java.io.*;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


@Slf4j
public class ExcelUtils {

	public static ResponseEntity<byte[]> makeExcelFile(String fileName, String[] titles, String[] fields, List<?> list) {
		return makeExcelFile(fileName, null, titles, fields, list);
	}

	public static ResponseEntity<byte[]> makeExcelFile(String fileName, String[] headers, String[] titles, String[] fields, List<?> list) {
		log.debug("create excel file name >>>>>>>>>>>> {}", fileName);
		SXSSFWorkbook wwb = null;
		ByteArrayOutputStream out = null;
		try {
			wwb = new SXSSFWorkbook();
			wwb.setCompressTempFiles(true);
			SXSSFSheet sXSSFSheet = wwb.createSheet();
			int i = 0;
			if(headers != null  && headers.length > 0) {
				for (i = 0; i < headers.length; i++) {
					Row row = sXSSFSheet.createRow(i);
					Cell cell = row.createCell(0);
					cell.setCellValue(headers[i]);
					CellRangeAddress cellrange = new CellRangeAddress(i, i, 0, titles.length-1);
					sXSSFSheet.addMergedRegion(cellrange);
				}
			}
			Row row = sXSSFSheet.createRow(i);
			for (int j = 0; j < titles.length; j++) {
				Cell cell = row.createCell(j);
				cell.setCellValue(titles[j]);
			}
			if (list != null && list.size() > 0 && fields != null && fields.length > 0) {
				for (int j = 0; j < list.size(); j++) {
					int rowNum = i + j+ 1;
					JSONObject json = JsonUtils.toJsonObject(list.get(j));
					row = sXSSFSheet.createRow(rowNum);
					int cellNum = 0;
					if ("序号".equals(titles[0])) {
						Cell cell0 = row.createCell(0);
						cell0.setCellValue((j + 1));
						cellNum = 1;
					}
					for (int k = 0; k < fields.length; k++) {
						String field = fields[k];
						String val = json.getString(field);
						Cell newCell = row.createCell(cellNum);
						newCell.setCellValue(StringUtils.isBlank(val) ? "" : val);
						cellNum++;
					}
				}
			}
			out = new ByteArrayOutputStream();
			wwb.write(out);
			byte[] byteArray = out.toByteArray();
			HttpHeaders httpHeaders = new HttpHeaders();
			httpHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
			httpHeaders.add("Content-Disposition","attachment; filename=" + new String(fileName.getBytes("ISO-8859-1"), "UTF-8"));
			ResponseEntity<byte[]> entity = new ResponseEntity<byte[]>(byteArray, httpHeaders, HttpStatus.OK);
			return entity;
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (out != null)
				IOUtils.closeQuietly(out);
			if (wwb != null)
				IOUtils.closeQuietly(wwb);
		}
		return new ResponseEntity<byte[]>(HttpStatus.NO_CONTENT);
	}

	public static ExcelHeaderDto[] getExcelHeaderDtos(List<Map<String, Object>> columns) {
		ExcelHeaderDto[] excelHeaderDtos = new ExcelHeaderDto[columns.size()];
		for (int i = 0; i < columns.size(); i++) {
			Map<String, Object> column = columns.get(i);
			excelHeaderDtos[i] = new ExcelHeaderDto((String) column.get("label"), (String) column.get("prop"));
		}
		return excelHeaderDtos;
	}

	public static void setCellValue(HSSFRow header, ExcelHeaderDto[] excelHeaderDtos, List<Map<String, Object>> entitys,
                                    HSSFSheet sheet) {
		ReadExcelDataUtils.configExcelHeader(header, excelHeaderDtos);
		if (entitys != null && !entitys.isEmpty()) {
			for (int i = 0; i < entitys.size(); i++) {
				Map<String, Object> entity = entitys.get(i);
				if (entity != null) {
					HSSFRow row = sheet.createRow(i + 1);
					for (int j = 0; j < excelHeaderDtos.length; j++) {
						String key = excelHeaderDtos[j].getPropertyName();
						String val = String.valueOf(entity.get(key));
						row.createCell(j).setCellValue(val);
					}
				}
			}
		}
	}

	public static ResponseEntity<byte[]> buildResponseEntity(File file) throws IOException {
		//获取文件
		InputStream is = new FileInputStream(file);
		byte[] body = new byte[is.available()];
		is.read(body);
		is.close();
		HttpHeaders headers = new HttpHeaders();
		//设置文件类型
		headers.add("Content-Disposition", "attchement;filename=" + file.getName());
		//设置Http状态码
		HttpStatus statusCode = HttpStatus.OK;
		//返回数据
		ResponseEntity<byte[]> entity = new ResponseEntity<byte[]>(body, headers, statusCode);
		return entity;
	}

	public static String deSymbol(String metadata){
		if(StringUtils.isBlank(metadata)){
			return metadata;
		}
		String regEx="[\\s*|\t|\n`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。， 、？]";
		String  rep = "";
		Pattern p = Pattern.compile(regEx);
		Matcher m = p.matcher(metadata);
		String newString = m.replaceAll(rep)
		.replace(String.valueOf((char)9),"")
		.replace(String.valueOf((char)10),"")
		.replace(String.valueOf((char)13),"")
		.replace(String.valueOf((char)32),"")
		.replace(String.valueOf((char)160),"")
		.trim();
		return newString;
	}
}
